using Daemon.Data.Substructure.Framework;
using Daemon.Model.Entities;
using Microsoft.EntityFrameworkCore.Metadata;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using gudusoft.gsqlparser;
using gudusoft.gsqlparser.nodes;
namespace Daemon.Infrustructure.EF.Executor
{
    public static class SqlParserUtil
    {
        private const EDbVendor _edbVendor = EDbVendor.dbvgeneric;

        private static readonly Dictionary<EComparisonType, FilterNodeOperator> _filterItemOperators = new Dictionary<EComparisonType, FilterNodeOperator>()
        {
            { EComparisonType.equalsTo, FilterNodeOperator.EqualTo },
            { EComparisonType.greaterThan, FilterNodeOperator.GreaterThan },
            { EComparisonType.greaterThanOrEqualTo, FilterNodeOperator.GreaterThanOrEqualTo },
            { EComparisonType.lessThan, FilterNodeOperator.LessThan },
            { EComparisonType.lessThanOrEqualTo, FilterNodeOperator.LessThanOrEqualTo },
            { EComparisonType.notEqualToExclamation, FilterNodeOperator.NotEqualTo },
            { EComparisonType.notEqualToBrackets, FilterNodeOperator.NotEqualTo },
            { EComparisonType.nullSafeEquals, FilterNodeOperator.IsWithIn }, // Sql server hasn't the key word '<=>', so use this key word for is within logic
		};

        // The isWithin reg is used for custom filter.
        public static Regex IsWithinReg => new Regex(@"(IsWithin\s+'(-?\d+)')");

        public static Regex IsWithinRegForExpression => new Regex(@"(<=>\s+'(-?\d+)')");

        // The IsSchools reg is used for schools list filter.
        public static Regex IsSchools => new Regex(@"\[Schools]\sLIKE\s'%((\s*\w+\s*,?\s*)+)%'");

        public static string IsDateTimeRegString => @"\[(?<field>{0})\]\s*(?<operator>[=<>]+)\s*\'(?<value>[^']+)\'";

        public static TExpression ParseExpression(string whereClause, Func<DateTime, DateTime> dateTimeConverter = null, string[] dateTimeFields = null)
        {
            TGSqlParser parser = new TGSqlParser(_edbVendor);
            return parser.parseExpression(ModifySpecialWhereClause(whereClause, dateTimeConverter, dateTimeFields));
        }

        /// <summary>
		/// If persistenceMap is not null, the db column name in expression will be replaced with entity property name
		/// Besides, we will replace entity property name to db column name before executing sql query
		/// </summary>
		/// <param name="expression"></param>
		/// <param name="persistenceMap"></param>
		/// <returns></returns>
		public static FilterSet GetFilterSetByExpression(TExpression expression, Dictionary<string, IProperty> persistenceMap = null)
        {
            if (expression == null)
            {
                return null;
            }

            FilterNode filterNode = GetFilterNode(expression);
            FilterSet filterSet = GetFilterSet(filterNode, persistenceMap);
            return filterSet;
        }

        public static FilterNode GetFilterNode(TExpression expression)
        {
            if (expression == null)
            {
                return null;
            }

            FilterNode filterNode = new FilterNode();

            filterNode.Operator = GetNodeOperator(expression);
            filterNode.LikeEscapeChar = expression.LikeEscapeOperand?.String;
            filterNode.ExprString = expression.String;

            if (expression.LeftOperand != null)
            {
                filterNode.LeftFilterNode = GetFilterNode(expression.LeftOperand);
            }

            if (expression.RightOperand != null)
            {
                filterNode.RightFilterNode = GetFilterNode(expression.RightOperand);
            }

            return filterNode;
        }

        public static IEnumerable<string> GetAllColumnsFromExpression(TExpression expression)
        {
            if (expression == null)
            {
                return new List<string>();
            }
            else if (expression.ExpressionType == EExpressionType.simple_object_name_t)
            {
                return new List<string>() { expression.String };
            }

            return GetAllColumnsFromExpression(expression.LeftOperand).Concat(GetAllColumnsFromExpression(expression.RightOperand));
        }

        public static IEnumerable<FilterInfo> GetAllFilterNameAndOperatorsFromExpression(TExpression expression)
        {
            if (expression == null)
            {
                return new List<FilterInfo>();
            }
            else if ((expression.LeftOperand != null && expression.LeftOperand.ExpressionType == EExpressionType.simple_object_name_t) || (expression.RightOperand != null && expression.RightOperand.ExpressionType == EExpressionType.simple_object_name_t))
            {
                return new List<FilterInfo>()
                {
                    new FilterInfo() { FieldName = expression.LeftOperand.ExpressionType == EExpressionType.simple_object_name_t ? expression.LeftOperand.String.Trim('[').Trim(']') : expression.RightOperand.String.Trim('[').Trim(']'), Operator = expression.OperatorToken.String },
                };
            }
            else
            {
                return GetAllFilterNameAndOperatorsFromExpression(expression.LeftOperand).Concat(GetAllFilterNameAndOperatorsFromExpression(expression.RightOperand));
            }
        }

        private static string ModifySpecialWhereClause(string whereClause, Func<DateTime, DateTime> dateTimeConverter = null, string[] dateTimeFields = null)
        {
            whereClause = WithinWhereClause(whereClause);
            whereClause = DateTimeWhereClause(whereClause, dateTimeConverter, dateTimeFields);
            return SchoolsWhereClause(whereClause);
        }

        private static string WithinWhereClause(string whereClause)
        {
            var matches = IsWithinReg.Matches(whereClause);
            foreach (Match match in matches)
            {
                if (match.Groups.Count != 3)
                {
                    continue;
                }

                var exp = match.Groups[0].Value;
                var replace = exp.Replace("IsWithin", "<=>");

                whereClause = whereClause.Replace(exp, replace);
            }

            return whereClause;
        }

        private static string SchoolsWhereClause(string whereClause)
        {
            var schoolsMatchs = IsSchools.Matches(whereClause);
            foreach (Match match in schoolsMatchs)
            {
                if (match.Groups.Count != 3)
                {
                    continue;
                }

                var exp = match.Groups[1].Value;
                var replaceToTrim = exp.Replace(" ", string.Empty);

                whereClause = whereClause.Replace(exp, replaceToTrim).Replace(",", "!");
            }

            return whereClause;
        }

        private static string DateTimeWhereClause(string whereClause, Func<DateTime, DateTime> dateTimeConverter = null, string[] dateTimeFields = null)
        {
            if (dateTimeFields == null || dateTimeFields.Length == 0)
            {
                return whereClause;
            }

            var dateTimeFieldsList = string.Join("|", dateTimeFields);
            var isDateTimeReg = new Regex(string.Format(IsDateTimeRegString, dateTimeFieldsList));
            return isDateTimeReg.Replace(whereClause, (match) =>
            {
                var result = match.ToString();
                var field = match.Groups["field"].Value;
                var op = match.Groups["operator"].Value;
                var value = match.Groups["value"].Value;

                if (dateTimeFields.Contains(field)
                    && DateTime.TryParseExact(value, "MM/dd/yyyy hh:mm tt", CultureInfo.InvariantCulture, DateTimeStyles.None, out DateTime dt))
                {
                    var startTime = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, 0);
                    if (dateTimeConverter != null)
                    {
                        startTime = dateTimeConverter(startTime);
                    }

                    var endTime = startTime.AddMinutes(1);
                    var startValue = startTime.ToString("yyyy-MM-dd HH:mm:ss");
                    var endValue = endTime.ToString("yyyy-MM-dd HH:mm:ss");

                    switch (op)
                    {
                        case "=":
                            result = $"([{field}] >= '{startValue}' AND [{field}] < '{endValue}')";
                            break;
                        case "<>":
                            result = $"([{field}] < '{startValue}' OR [{field}] >= '{endValue}')";
                            break;
                        case "<":
                            result = $"[{field}] < '{startValue}'";
                            break;
                        case "<=":
                            result = $"[{field}] < '{endValue}'";
                            break;
                        case ">":
                            result = $"[{field}] >= '{endValue}'";
                            break;
                        case ">=":
                            result = $"[{field}] >= '{startValue}'";
                            break;
                        default:
                            throw new InvalidOperationException($"The filter operator '{op}' is invalid for datetime.");
                    }
                }

                return result;
            });
        }

        private static FilterNodeOperator GetNodeOperator(TExpression expression)
        {
            switch (expression.ExpressionType)
            {
                case EExpressionType.logical_and_t:
                    return FilterNodeOperator.And;
                case EExpressionType.logical_or_t:
                    return FilterNodeOperator.Or;
                case EExpressionType.null_t:
                    return expression.NotToken != null ? FilterNodeOperator.IsNotNull : FilterNodeOperator.IsNull;
                case EExpressionType.in_t:
                    return expression.NotToken != null ? FilterNodeOperator.NotIn : FilterNodeOperator.In;
                case EExpressionType.pattern_matching_t:
                    if (expression.OperatorToken != null && string.Equals(expression.OperatorToken.String, "like", StringComparison.OrdinalIgnoreCase))
                    {
                        return expression.NotToken != null ? FilterNodeOperator.DoesNotContain : FilterNodeOperator.Contains;
                    }

                    break;
                default:
                    // Sql server hasn't the key word '<=>', so use this key word for is within logic
                    if (expression.ComparisonType == EComparisonType.notEqualToBrackets && string.Equals(expression.OperatorToken.String, "<=>", StringComparison.OrdinalIgnoreCase))
                    {
                        return FilterNodeOperator.IsWithIn;
                    }

                    if (_filterItemOperators.TryGetValue(expression.ComparisonType, out FilterNodeOperator itemOperator))
                    {
                        return itemOperator;
                    }

                    break;
            }

            return FilterNodeOperator.Empty;
        }

        private static FilterSet GetFilterSet(FilterNode filterNode, Dictionary<string, IProperty> persistenceMap)
        {
            if (filterNode.IsEmptyOperator)
            {
                return GetFilterSet(filterNode.LeftFilterNode, persistenceMap);
            }
            else if (filterNode.IsConnectionOperator)
            {
                FilterSet filterSet = new FilterSet()
                {
                    FilterItems = new List<FilterItem>(),
                    FilterSets = new List<FilterSet>(),
                    LogicalOperator = filterNode.Operator == FilterNodeOperator.And ? LogicalOperator.And : LogicalOperator.Or,
                };

                FilterNode leftNode = FilterNode.GetNotEmptyNode(filterNode.LeftFilterNode);
                if (leftNode != null)
                {
                    if (leftNode.IsSingleExpr)
                    {
                        filterSet.FilterItems.Add(CreateFilterItem(leftNode, persistenceMap));
                    }
                    else
                    {
                        filterSet.FilterSets.Add(GetFilterSet(leftNode, persistenceMap));
                    }
                }

                FilterNode rightNode = FilterNode.GetNotEmptyNode(filterNode.RightFilterNode);
                if (rightNode != null)
                {
                    if (rightNode.IsSingleExpr)
                    {
                        filterSet.FilterItems.Add(CreateFilterItem(rightNode, persistenceMap));
                    }
                    else
                    {
                        filterSet.FilterSets.Add(GetFilterSet(rightNode, persistenceMap));
                    }
                }

                return filterSet;
            }
            else
            {
                return new FilterSet()
                {
                    FilterItems = new List<FilterItem>()
                    {
                        CreateFilterItem(filterNode, persistenceMap),
                    },
                    FilterSets = new List<FilterSet>(),
                    LogicalOperator = LogicalOperator.And,
                };
            }
        }

        private static FilterItem CreateFilterItem(FilterNode filterNode, Dictionary<string, IProperty> persistenceMap)
        {
            FilterNodeOperator parentNodeOperator = filterNode.Operator;
            string exprValue = string.Empty;
            string valueList = string.Empty;
            if (filterNode.RightFilterNode != null)
            {
                const char apostrophe = '\'', percentSign = '%', leftBrackets = '(', rightBrackets = ')';
                exprValue = filterNode.RightFilterNode.ExprString;
                exprValue = exprValue[0] == apostrophe ? exprValue.Substring(1) : exprValue;
                exprValue = exprValue[exprValue.Length - 1] == apostrophe ? exprValue.Substring(0, exprValue.Length - 1) : exprValue;
                if (parentNodeOperator == FilterNodeOperator.Contains || parentNodeOperator == FilterNodeOperator.DoesNotContain)
                {
                    bool startsWith = true, endsWith = true;
                    if (exprValue[0] == percentSign)
                    {
                        exprValue = exprValue.Substring(1);
                        startsWith = false;
                    }

                    if (exprValue[exprValue.Length - 1] == percentSign)
                    {
                        exprValue = exprValue.Substring(0, exprValue.Length - 1);
                        endsWith = false;
                    }

                    if (!string.IsNullOrWhiteSpace(filterNode.LikeEscapeChar))
                    {
                        string escape = filterNode.LikeEscapeChar[1].ToString();
                        string doubleEscape = escape + escape;
                        exprValue = string.Join(escape, Regex.Split(exprValue, doubleEscape).Select(r => r.Replace(escape, string.Empty)));
                    }

                    if (startsWith && endsWith)
                    {
                        parentNodeOperator = FilterNodeOperator.EqualTo;
                    }
                    else if (!startsWith && endsWith)
                    {
                        parentNodeOperator = FilterNodeOperator.EndsWith;
                    }
                    else if (!endsWith && startsWith)
                    {
                        parentNodeOperator = FilterNodeOperator.StartsWith;
                    }
                }
                else if (parentNodeOperator == FilterNodeOperator.In || parentNodeOperator == FilterNodeOperator.NotIn)
                {
                    exprValue = exprValue[0] == leftBrackets ? exprValue.Substring(1) : exprValue;
                    exprValue = exprValue[exprValue.Length - 1] == rightBrackets ? exprValue.Substring(0, exprValue.Length - 1) : exprValue;
                    string[] pureExprValueList = GetPureExprValueList(exprValue);
                    valueList = "[" + string.Join(",", pureExprValueList.Select(r => "\"" + r + "\"")) + "]";
                }
            }

            return new FilterItem
            {
                FieldName = ConvertDBColumnNameToEntityPropertyName(filterNode.LeftFilterNode.ExprString.Trim('[').Trim(']'), persistenceMap),
                Value = exprValue,
                ValueList = valueList,
                Operator = parentNodeOperator.ToString(),
            };
        }

        /// <summary>
        /// We have a property named 'DBName' in many grid definitions in the front end.
        /// for example
        /// 					FieldName: "LocalId",
        /// 					DisplayName: "Local ID",
        /// 					DBName: "local_id",
        /// 					Width: '150px',
        /// 					type: "string"
        /// In api side, we handle domain entity's property only.
        /// So we need to convert db column name to entity property name when apply the filter.
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="persistenceMap"></param>
        /// <returns></returns>
        private static string ConvertDBColumnNameToEntityPropertyName(string columnName, Dictionary<string, IProperty> persistenceMap)
        {
            if (persistenceMap == null)
            {
                return columnName;
            }

            var propertyInfo = persistenceMap.FirstOrDefault(p => string.Equals(p.Value.Name, columnName, StringComparison.OrdinalIgnoreCase));

            return propertyInfo.Key ?? columnName;
        }

        private static string[] GetPureExprValueList(string exprValue)
        {
            // remove ' and split exprValue as following cases
            // - 'firstName1, lastName1','firstName2, lastName2'
            // - 'firstName1, lastName1'
            // - 1,2
            // - 1
            if (exprValue.StartsWith("'") && exprValue.EndsWith("'"))
            {
                exprValue = exprValue.Substring(1, exprValue.Length - 2);
                if (exprValue.Contains(','))
                {
                    string[] separatingStrings = { "','" };
                    return exprValue.Split(separatingStrings, System.StringSplitOptions.RemoveEmptyEntries);
                }
                else
                {
                    return exprValue.Split(',');
                }
            }
            else
            {
                return exprValue.Split(',');
            }
        }
    }

    public class FilterNode
    {
        /// <summary>
        /// The left filter node from owner.
        /// </summary>
        public FilterNode LeftFilterNode { get; set; }

        /// <summary>
        /// The right filter node from owner.
        /// </summary>
        public FilterNode RightFilterNode { get; set; }

        /// <summary>
        /// Gets or sets the operation.
        /// </summary>
        public FilterNodeOperator Operator { get; set; }

        public string LikeEscapeChar { get; set; }

        public string ExprString { get; set; }

        public bool IsConnectionOperator => Operator == FilterNodeOperator.And || Operator == FilterNodeOperator.Or;

        public bool IsEmptyOperator => Operator == FilterNodeOperator.Empty;

        public bool IsSingleExpr
        {
            get
            {
                FilterNode leftNode = GetNotEmptyNode(LeftFilterNode);
                return leftNode != null && leftNode.IsEmptyOperator;
            }
        }

        public static FilterNode GetNotEmptyNode(FilterNode filterNode)
        {
            if (filterNode.LeftFilterNode != null)
            {
                return filterNode.IsEmptyOperator ? GetNotEmptyNode(filterNode.LeftFilterNode) : filterNode;
            }

            return filterNode;
        }
    }

    public enum FilterNodeOperator
    {
        EqualTo,
        GreaterThan,
        GreaterThanOrEqualTo,
        LessThan,
        LessThanOrEqualTo,
        NotEqualTo,
        IsNotNull,
        IsNull,
        DoesNotContain,
        Contains,
        NotIn,
        In,
        IsWithIn,
        StartsWith,
        EndsWith,
        And,
        Or,
        Empty,
    }

    public static class FilterNodeOperatorExtension
    {
        public static string ToNumberOperatorString(this FilterNodeOperator that)
        {
            switch (that)
            {
                case FilterNodeOperator.EqualTo:
                    return "=";
                case FilterNodeOperator.GreaterThan:
                    return ">";
                case FilterNodeOperator.GreaterThanOrEqualTo:
                    return ">=";
                case FilterNodeOperator.LessThan:
                    return "<";
                case FilterNodeOperator.LessThanOrEqualTo:
                    return "<=";
                case FilterNodeOperator.NotEqualTo:
                    return "<>";
                default:
                    return string.Empty;
            }
        }
    }
}